﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace Gugu.management.Excel
{

    class Reader
    {
        readonly Application _xlApp;
        readonly Workbook _wb;

        public Reader()
        {
            try
            {
                _xlApp = new Application();
                _wb = _xlApp.Workbooks.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Template.xlsm"));
                _xlApp.DisplayAlerts = false;
                _xlApp.Visible = false;

            }
            catch (Exception)
            {
                _xlApp.Quit();
                Marshal.ReleaseComObject(_xlApp);
                GC.Collect();
                throw;
            }
        }

        /// <summary>
        /// Save file out
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="answer"></param>
        public void SaveFile(string filePath, System.Windows.Forms.DialogResult answer)
        {
            _Worksheet ws = _wb.Sheets["Job Notes"];
            ws.Activate();
            //Macro marcro = new Macro();
            _xlApp.Run("Macroname");
            ws = _wb.Sheets[_wb.Sheets.Count];
            ws.Activate();
            var fileFullPath = Path.Combine(filePath, DateTime.Now.ToString("yyyy MMMM dd") + ".xlsm");
            _wb.Close(true, fileFullPath, Missing.Value);


            //Send a message to confirm weather user wants to open output file
            if (answer == System.Windows.Forms.DialogResult.Yes)
            {
                _xlApp.Visible = true;
                _xlApp.Workbooks.Open(fileFullPath);
            }
            else
            {
                _xlApp.Quit();
                Marshal.ReleaseComObject(_xlApp);
                GC.Collect();
            }
        }



        /// <summary>
        /// write out excell file
        /// </summary>
        /// <param name="filePath"></param>
        public void WriteExcell(string filePath)
        {
            try
            {
                if (_xlApp == null) return;
                var fileName = new FileInfo(filePath);

                Worksheet ws = _wb.Sheets.Add(After: _wb.Sheets[_wb.Sheets.Count]);
                ws.Name = Path.GetFileNameWithoutExtension(fileName.Name);
                var data = FillData(null);

                var range = ws.Range["A1"];//Index (1,1)

                ws.Hyperlinks.Add(range,
                    fileName.FullName,
                    Type.Missing,
                    fileName.FullName,
                    fileName.FullName);

                var i = 2;
                foreach (var arow in data)
                {
                    var j = 1;
                    foreach (var cell in arow)
                    {
                        range[i, j] = cell;
                        j++;
                    }
                    i++;
                }
            }
            catch (Exception)
            {
                if (_xlApp != null)
                {
                    _xlApp.Quit();
                    Marshal.ReleaseComObject(_xlApp);
                }
                GC.Collect();
                throw;
            }

        }



        /// <summary>
        /// Format file excell
        /// </summary>
        /// <param name="trusses"></param>
        /// <returns></returns>
        private static IEnumerable<string[]> FillData(ICollection<object> trusses)
        {
            try
            {
                var columns = new List<string>();

                var rows = new List<string[]>();
                columns.Add("Summary");
                rows.Add(columns.ToArray<string>());

                rows.Add(columns.ToArray<string>());

                columns = new List<string> { "Trusses In Document" };

                rows.Add(columns.ToArray<string>());
                columns = new List<string>
                {
                    "",
                    "Truss Name",
                    "TCLL",
                    "Ground Snow",
                    "Version",
                    "Spacing"
                };
                rows.Add(columns.ToArray<string>());

                return rows;
            }
            catch (Exception ex)
            {
                var e = new Exception("Truss Name: " +  "\n" + ex.Message);
                throw e;
            }
        }
        /// <summary>
        /// Read file configulation file
        /// Throw exception when can not read parameters
        /// </summary>
        /// <param name="filename"></param>
        public static List<Account> ReadExcell(string filename)
        {
            Application xlApp = null;
            try
            {
                xlApp = new Application { Visible = false, DisplayAlerts = false };

                var wb = xlApp.Workbooks.Open(filename);
                //Sheet Name here
                var ws = (Worksheet)wb.Worksheets[1];
                if (ws == null)
                {
                    return null;
                }

                var excelRange = ws.UsedRange;
                var list = new List<Account>();
                const int startRow = 5;
                foreach (Range row in excelRange.Rows)
                {
                    //startRow++;
                    if (String.IsNullOrWhiteSpace(row.Cells[startRow, 1].text)) continue;
                    var account = new Account();
                    var col = 0;
                    account.Index = int.Parse(row.Cells[startRow, ++col].text);
                    account.Enable = "True".Equals(row.Cells[startRow, ++col].text);
                    account.SiteName = row.Cells[startRow, ++col].text;
                    Status status;
                    Enum.TryParse(row.Cells[startRow, ++col].text, false, out status);
                    account.Status = status;
                    account.Name = row.Cells[startRow, ++col].text;
                    account.UserName = row.Cells[startRow, ++col].text;
                    account.Password = row.Cells[startRow, ++col].text;
                    account.Descrpition = row.Cells[startRow, ++col].text;
                    list.Add(account); //record one
                }

                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
                GC.Collect();
                
                return list;
            }
            catch (Exception)
            {
                if (xlApp != null)
                {
                    xlApp.Quit();
                    Marshal.ReleaseComObject(xlApp);
                }
                GC.Collect();
                throw;
            }

        }
    }
}
